package cn.elwy.kit.activity.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import cn.elwy.common.util.sort.SortUtil;
import cn.elwy.kit.activity.Employee;

public class RandomGrouping {

    public static final String XLS = "xls";
    public static final String XLSX = "xlsx";
    public static final String 参会人员统计 = "参会人员统计";
    public static final String 桌长名单 = "桌长名单";
    public static final String 博品桌次安排 = "博品桌次安排";
    public static final String 用餐桌次安排 = "用餐桌次安排";
    public static final String 第i桌桌长 = "第{0}桌\r\n桌长：";

    private List<Employee> chargehandList = new ArrayList<Employee>();
    private List<Employee> employeeList = new ArrayList<Employee>();
    private Workbook workbook;
    private File srcFile;
    private File destFile;
    private int number;

    public RandomGrouping(File srcFile, File destFile, int number) {
        this.srcFile = srcFile;
        this.destFile = destFile;
        this.number = number;
    }

    public void loadData() throws IOException {
        workbook = getWorkbook(srcFile.getAbsolutePath());
        int numberOfSheets = workbook.getNumberOfSheets();
        chargehandList = new ArrayList<Employee>();
        for (int k = 0; k < numberOfSheets; k++) {
            Sheet sheet = workbook.getSheetAt(k);
            if (sheet.getSheetName().equalsIgnoreCase(参会人员统计)) {
                readSheet(sheet);
            } else if (sheet.getSheetName().equalsIgnoreCase(参会人员统计)) {
                readSheet(sheet);
            }
        }
    }

    /**
     * 获取工作簿对象
     * @param filePath 文件路径
     * @return
     * @throws IOException
     * @throws Exception
     */
    private Workbook getWorkbook(String filePath) throws IOException {
        FileInputStream is = new FileInputStream(filePath);
        try {
            String extName = getExtName(filePath);
            if (XLS.equalsIgnoreCase(extName)) {// 2003
                return new HSSFWorkbook(is);
            } else {// 2007
                return new XSSFWorkbook(is);
            }
        } finally {
            is.close();
        }
    }

    public static String getExtName(String fileName) {
        int index = fileName.lastIndexOf(".");
        return index != -1 ? fileName.substring(index + 1) : "";
    }

    private void readSheet(Sheet sheet) {
        int rows = sheet.getPhysicalNumberOfRows();
        int x = -1;
        for (int r = 1; r < rows; r++) {
            // 定义 row
            Row row = sheet.getRow(r);
            if (row != null) {
                int cells = row.getPhysicalNumberOfCells();
                if (x == -1) {
                    x = r;
                } else {
                    if (cells < 1) {
                        continue;
                    }
                    Employee e = new Employee();
                    for (short c = 0; c < cells; c++) {
                        Cell cell = row.getCell(c);
                        if (cell != null) {
                            String value = getExcelCellFormattedValue(cell);

                            if (c == 0) {
                                e.setCode(value);
                            } else if (c == 1) {
                                e.setArea(value);
                            } else if (c == 2) {
                                e.setDept(value);
                            } else if (c == 3) {
                                e.setName(value);
                            } else if (c == 4) {
                                e.setPhone(value);
                            } else if (c == 5) {
                                e.setRemark(value);
                            }
                        }
                    }
                    if (e.getRemark() == null || "".equals(e.getRemark())) {
                        employeeList.add(e);
                    } else {
                        chargehandList.add(e);
                    }
                }
            }
        }
    }

    public void saveData() throws IOException {
        workbook = getWorkbook(srcFile.getAbsolutePath());
        int numberOfSheets = workbook.getNumberOfSheets();

        Collections.shuffle(employeeList);// 打乱的序列
        Collections.shuffle(chargehandList);// 打乱的序列

        System.out.println(chargehandList);
        SortUtil.sort(chargehandList, false, "remark");
        System.out.println(chargehandList);

        for (int k = 0; k < numberOfSheets; k++) {
            Sheet sheet = workbook.getSheetAt(k);
            if (sheet.getSheetName().equalsIgnoreCase(用餐桌次安排)) {
                grouping(sheet, chargehandList, employeeList, number);
            } else if (sheet.getSheetName().equalsIgnoreCase(博品桌次安排)) {
                grouping(sheet, chargehandList, employeeList, number);
            }
        }
        FileOutputStream fo = null;
        try {
            fo = new FileOutputStream(destFile);
            workbook.write(fo);
        } finally {
            if (fo != null) {
                fo.close();
            }
        }
    }

    public static String getExcelCellFormattedValue(Cell cell) {
        return getExcelCellFormattedValue(cell, null);
    }

    public static String getExcelCellFormattedValue(Cell cell, FormulaEvaluator evaluator) {
        if (cell == null) {
            return "";
        }
        DataFormatter formatter = new DataFormatter();
        return formatter.formatCellValue(cell, evaluator);
    }

    public static void main(String[] args) throws Exception {

        File file = new File("X:/2015年会参会人员统计.xls");
        File file1 = new File("X:/2015年会参会人员统计1.xls");
        RandomGrouping randomGrouping = new RandomGrouping(file, file1, 10);
        randomGrouping.loadData();
        List<Employee> employeeList2 = randomGrouping.employeeList;
        Collections.shuffle(employeeList2);// 打乱的序列

        randomGrouping.saveData();
    }

    /**
     * @param chargehandList 组长列表
     * @param employeeList 员工列表
     * @param limit 每桌人数
     */
    protected void grouping(Sheet sheet, List<Employee> chargehandList, List<Employee> employeeList, int limit) {
        int size = employeeList.size();
        int count = 0;
        int x = 0, y = 1;
        int chargehandSize = chargehandList.size();
        for (int start = 0; start < size; start++) {
            // chargehand 输出组长
            if (start % (limit - 1) == 0) {
                Employee chargehand = null;
                if (count < chargehandSize) {
                    chargehand = chargehandList.get(count);
                } else { // 组长人数不够随机指定一个组长
                    chargehand = employeeList.get(start);
                    start++;
                    if (start >= size) {
                        break;
                    }
                }
                count++;
                out(sheet, chargehand, x, y);
                String replace = 第i桌桌长.replaceAll("\\{0\\}", count + "") + "\r\n" + chargehand.getName();
                sheet.getRow(y).getCell(x + 2).setCellValue(replace);
                y++;
            }
            Employee employee = employeeList.get(start);
            out(sheet, employee, x, y);
            y++;
        }
        int cursor = size % (limit - 1) + 1;
        // 组长人数过多，以普通员工处理
        for (int start = count; start < chargehandSize; start++) {
            // chargehand 输出组长
            if (cursor % (limit - 1) == 0) {
                Employee chargehand = null;
                chargehand = chargehandList.get(start);
                count++;
                out(sheet, chargehand, x, y);
                String replace = 第i桌桌长.replaceAll("\\{0\\}", count + "") + "\r\n" + chargehand.getName();
                sheet.getRow(y).getCell(x + 2).setCellValue(replace);
                y++;
                start++;
            }
            Employee employee = chargehandList.get(start);
            out(sheet, employee, x, y);
            y++;
            cursor++;
        }
    }

    private void out(Sheet sheet, Employee employee, int x, int y) {
        // 序号 部门 桌次 姓名 电话
        Cell cell = sheet.getRow(y).getCell(x);
        if (cell == null) {
            System.out.println();
        }
        cell.setCellValue(y);
        sheet.getRow(y).getCell(x).setCellValue(y);
        employee.getRemark();
        sheet.getRow(y).getCell(x + 1).setCellValue(employee.getDept());
        sheet.getRow(y).getCell(x + 3).setCellValue(employee.getName());
        sheet.getRow(y).getCell(x + 4).setCellValue(employee.getPhone());
    }

}
